﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using MySql.Data.MySqlClient;

using BMS.Models;
using BMS.Utilities;

namespace BMS.Models.DataAccess
{
    public class InvoiceRepository
    {
        private DatabaseAccess db = new DatabaseAccess();

        public List<InvoiceModel> GetAllInvoices()
        {
            List<InvoiceModel> allInvoices = new List<InvoiceModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".invoice";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    InvoiceModel invoice = new InvoiceModel();

                    invoice.InvoiceNo = dataReader["vc_fldInvoiceNo"] + "";
                    invoice.NoOfQuotations = int.Parse(dataReader["in_fldNoOfQuotations"] + "");
                    invoice.TotalAmount = decimal.Parse(dataReader["dc_fldTotalAmount"] + "");
                    invoice.isDebitNote = dataReader["vc_fldIsDebitNote"] + "";
                    invoice.DNNo = dataReader["vc_fldDebitNoteNo"] + "";
                    invoice.isQuotation = dataReader["vc_fldIsQuotation"] + "";
                    invoice.QuotNo = dataReader["vc_fldQuotationNo"] + "";
                    invoice.isPurchaseOrder = dataReader["vc_fldIsPO"] + "";
                    invoice.PONo = dataReader["vc_fldPONo"] + "";
                    invoice.isWorkOrder = dataReader["vc_fldIsWO"] + "";
                    invoice.WONo = dataReader["vc_fldWONo"] + "";
                    invoice.ReceiptNo = dataReader["vc_fldReceiptNo"] + "";
                    invoice.ReceiptDate = DateTime.Parse(dataReader["dt_fldReceipt"] + "");
                    invoice.InvoiceCreationDate = DateTime.Parse(dataReader["dt_fldCreate"] + "");
                    invoice.IsValid = dataReader["vc_fldValid"] + "";

                    allInvoices.Add(invoice);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allInvoices;
        }

        public InvoiceModel GetInvoice(String invoiceNo)
        {
            InvoiceModel foundInvoice = new InvoiceModel();

            List<InvoiceModel> tempInvoicesList = GetAllInvoices();
            foundInvoice = tempInvoicesList.Find(x => x.InvoiceNo == invoiceNo);

            return foundInvoice;
        }

        public List<InvoiceItemModel> GetAllInvoiceItems()
        {
            List<InvoiceItemModel> allInvoiceItems = new List<InvoiceItemModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".invoiceitem";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    InvoiceItemModel invoiceItem = new InvoiceItemModel();

                    invoiceItem.InvoiceNo = dataReader["vc_fldInvoiceNo"] + "";
                    invoiceItem.QuotationNo = dataReader["vc_fldQuotationNo"] + "";

                    allInvoiceItems.Add(invoiceItem);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allInvoiceItems;
        }

        public List<InvoiceItemModel> GetInvoiceItems_ByInvoiceNo(string InvoiceNo)
        {
            List<InvoiceItemModel> allInvoiceItems = new List<InvoiceItemModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".invoiceitem WHERE vc_fldInvoiceNo ='" + InvoiceNo + "'";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    InvoiceItemModel invoiceItem = new InvoiceItemModel();

                    invoiceItem.InvoiceNo = dataReader["vc_fldInvoiceNo"] + "";
                    invoiceItem.QuotationNo = dataReader["vc_fldQuotationNo"] + "";

                    allInvoiceItems.Add(invoiceItem);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allInvoiceItems;
        }

        public bool Insert(InvoiceModel invoiceModel)
        {
            bool isInserted = false;

            try
            {
                String query = "INSERT INTO " + Configuration.DatabaseName + ".invoice (vc_fldInvoiceNo, in_fldNoOfQuotations, dc_fldTotalAmount, vc_fldIsDebitNote, vc_fldDebitNoteNo,"
                    + "vc_fldIsQuotation, vc_fldQuotationNo, vc_fldIsPO, vc_fldPONo, vc_fldIsWO, vc_fldWONo, vc_fldReceiptNo, dt_fldReceipt, dt_fldCreate," + "vc_fldValid) VALUES('" + invoiceModel.InvoiceNo + "', '" + invoiceModel.NoOfQuotations
                    + "', '" + invoiceModel.TotalAmount + "', '" + invoiceModel.isDebitNote + "', '" + invoiceModel.DNNo + "', '" + invoiceModel.isQuotation + "', '" + invoiceModel.QuotNo + "', '" + invoiceModel.isPurchaseOrder + "', '" + invoiceModel.PONo
                    + "', '" + invoiceModel.isWorkOrder + "', '" + invoiceModel.WONo + "', '" + invoiceModel.ReceiptNo + "', '" + invoiceModel.ReceiptDate.ToString("yyyy-MM-dd H:mm:ss")
                    + "', '" + invoiceModel.InvoiceCreationDate.ToString("yyyy-MM-dd H:mm:ss") + "', '" + invoiceModel.IsValid + "')";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isInserted = true;
            }
            catch (Exception e)
            {
                isInserted = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isInserted;
        }

        public bool Insert(List<InvoiceItemModel> invoiceItemModel)
        {
            bool isInserted = false;


            foreach (InvoiceItemModel iitem in invoiceItemModel)
            {
                try
                {
                    String query = "INSERT INTO " + Configuration.DatabaseName + ".invoiceitem (vc_fldInvoiceNo, vc_fldQuotationNo) VALUES('" + iitem.InvoiceNo
                        + "', '" + iitem.QuotationNo + "')";
                    db.DBConnectionOn();
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, db.connection);

                    //Execute command
                    cmd.ExecuteNonQuery();

                    isInserted = true;
                }
                catch (Exception e)
                {
                    isInserted = false;
                    throw;
                }
                finally
                {
                    db.DBConnectionOff();
                }
            }

            return isInserted;
        }
        
        private List<String> GetInvoiceNoList()
        {
            List<String> InvNoList = new List<string>();

            List<InvoiceModel> invoiceList = GetAllInvoices();

            if (invoiceList.Count > 0)
            {
                foreach (InvoiceModel inv in invoiceList)
                    InvNoList.Add(inv.InvoiceNo);
            }


            return InvNoList;
        }

        public String GetNextInvoiceNo()
        {
            String invoiceNo = "";

            List<String> InvNoList = GetInvoiceNoList();

            if (InvNoList.Count > 0)
            {
                List<int> tempList = new List<int>();

                // Step 1: Split the invoice no - [0]: FM Name, [1]: IN (Invoice), [2]: YYMM (Year & Month), [3]: xxxxx
                foreach (String invNo in InvNoList)
                {
                    String[] InvNoSplit = invNo.Split('-');

                    tempList.Add(int.Parse(InvNoSplit[3]));
                }

                // Step 2: Sort the quotation no in ascending order
                tempList = tempList.OrderBy(v => v).ToList();

                // Step 3: Generate a new quotation no
                int tempNo = tempList[tempList.Count - 1] + 1;
                invoiceNo = tempNo.ToString("00000");
            }
            else
                invoiceNo = "00001";

            return invoiceNo;
        }
    }
}